package com.firegod.course.service;

import com.firegod.servicebase.dao.StudentRepository;
import com.firegod.servicebase.dao.UserRepository;
import com.firegod.servicebase.entity.Student;
import com.firegod.servicebase.entity.User;
import com.firegod.servicebase.exceptionhandler.exception.GuliException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.DigestUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ExcelService {

    @Autowired
    private StudentRepository studentRepository;

    @Autowired
    private UserRepository userRepository;

    /**
     * 数据库中不存在学生，直接用来注册，密码固定
     */
    public List<Student> registerStudentFromExcel(MultipartFile file) throws Exception {
        Map<String, List<String>> students = readExcel(file);
        List<Student> result = new ArrayList<>();
        var studentId = students.get("userId");
        var studentName = students.get("userName");
        var studentEmail = students.get("email");
        var studentMobile = students.get("mobile");
        for (int i = 0; i < studentId.size(); i++) {
            User user = userRepository.getByUserId(studentId.get(i));
            Student student;
            if (user instanceof Student) {
                student = (Student) user;
            } else if (user == null) {
                student = new Student(studentId.get(i), studentName.get(i));
                if (null != studentEmail) {
                    student.setEmail(studentEmail.get(i));
                }
                if (null != studentMobile) {
                    student.setMobile(studentMobile.get(i));
                }
                student.setPassword(DigestUtils.md5DigestAsHex("123456".getBytes(StandardCharsets.UTF_8)));
            } else {
                throw GuliException.from("第" + i + "个学号:" + studentId.get(i) + " 已被注册为老师，导入失败！");
            }
            result.add(student);
        }
        result.forEach(s -> studentRepository.save(s));
        studentRepository.flush();
        return result;
    }


    public Map<String, List<String>> readExcel(MultipartFile file) throws Exception {
        String fileName = file.getOriginalFilename();
        if (fileName != null) {
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
            System.out.println(fileType);
            if (fileType.equalsIgnoreCase("xlsx")) {
                return readXlsx(file);
            } else if (fileType.equalsIgnoreCase("xls")) {
                return readXls(file);
            } else {
                throw new RuntimeException("不支持的文件格式");
            }
        } else {
            throw new FileNotFoundException();
        }
    }

    public Map<String, List<String>> readXls(MultipartFile file) throws Exception {
        InputStream is = new ByteArrayInputStream(file.getBytes());

        // HSSFWorkbook 标识整个excel
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        Map<String, List<String>> result = new HashMap<>();
        int size = hssfWorkbook.getNumberOfSheets();

        // 循环每一页，并处理当前循环页
        for (int numSheet = 0; numSheet < size; numSheet++) {
            // HSSFSheet 标识某一页
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null)
                continue;

            Map<Integer, String> pos = new HashMap<>();    //每一列对应的位置

            //拿到列名
            HSSFRow firstRow = hssfSheet.getRow(0);
            if (firstRow != null) {
                int maxColIx = firstRow.getLastCellNum();
                //找到列名对应的列
                for (int i = firstRow.getFirstCellNum(); i <= maxColIx; i++) {
                    HSSFCell cell = firstRow.getCell(i);
                    if (null == cell) {
                        continue;
                    }
                    String key = excelNameToUserName(cell.getStringCellValue());
                    pos.put(i, key);
                    result.put(key, new ArrayList<>());
                }
            }


            //读取Row,从第二行开始
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                // HSSFRow表示行
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                int minColIx = hssfRow.getFirstCellNum();
                int maxColIx = hssfRow.getLastCellNum();

                // 遍历该行，获取处理每个cell元素
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    String colName = pos.get(colIx);    //当前列的名字
                    if (colName == null)
                        continue;
                    // HSSFCell 表示单元格
                    HSSFCell cell = hssfRow.getCell(colIx);
                    if (cell == null) {
                        continue;
                    }
                    cell.setCellType(CellType.STRING);
                    result.get(colName).add(cell.getStringCellValue());
                }
            }
        }
        return result;
    }


    public Map<String, List<String>> readXlsx(MultipartFile file) throws Exception {
        InputStream is = new ByteArrayInputStream(file.getBytes());

        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        Map<String, List<String>> result = new HashMap<>();
        int size = xssfWorkbook.getNumberOfSheets();

        // 循环每一页，并处理当前循环页
        for (int numSheet = 0; numSheet < size; numSheet++) {
            // HSSFSheet 标识某一页
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null)
                continue;

            Map<Integer, String> pos = new HashMap<>();    //每一列对应的位置

            //拿到列名
            XSSFRow firstRow = xssfSheet.getRow(0);
            if (firstRow != null) {
                int maxColIx = firstRow.getLastCellNum();
                //找到列名对应的列
                for (int i = firstRow.getFirstCellNum(); i < maxColIx; i++) {
                    XSSFCell cell = firstRow.getCell(i);
                    if (null == cell) {
                        continue;
                    }
                    String key = excelNameToUserName(cell.getStringCellValue());
                    pos.put(i, key);
                    result.put(key, new ArrayList<>());
                }
            }

            //读取Row,从第二行开始
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                // HSSFRow表示行
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                int minColIx = xssfRow.getFirstCellNum();
                int maxColIx = xssfRow.getLastCellNum();

                // 遍历该行，获取处理每个cell元素
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    String colName = pos.get(colIx);    //当前列的名字
                    if (colName == null)
                        continue;
                    // HSSFCell 表示单元格
                    XSSFCell cell = xssfRow.getCell(colIx);
                    if (cell == null) {
                        continue;
                    }
                    cell.setCellType(CellType.STRING);
                    result.get(colName).add(cell.getStringCellValue());
                }
            }
        }
        return result;
    }

    private String excelNameToUserName(String excelName) {
        if (excelName.contains("邮件") || excelName.contains("邮箱") || excelName.toLowerCase().contains("mail")) {
            return "email";
        } else if (excelName.contains("学号") || excelName.toLowerCase().contains("id")) {
            return "userId";
        } else if (excelName.contains("姓名") || excelName.toLowerCase().contains("name")) {
            return "userName";
        } else if (excelName.contains("联系方式") || excelName.contains("电话") || excelName.contains("手机")
                || excelName.toLowerCase().contains("mobile") || excelName.toLowerCase().contains("phone")) {
            return "mobile";
        } else {
            return "";
        }
    }
}
